const cc = require('./conCtl');
const js = require('./jsCtl')
const conf = require('./config.json');
const mysql = require('mysql');

// var config = {
//     host: '101.132.96.48',//'localhost',
//     user: 'root',
//     password: '123321333',
//     database: 'wuyoujiedai'
// };

const pool = mysql.createPool(conf.db);

module.exports.pool = pool;

//数据库专用缓存对象
const db_store = js.cloneObject(cc.store);

/** 按key清楚缓存
 * 
 * @param {String} key 
 */
module.exports.destroyStore = (key) => { db_store.destroy(key); }

module.exports.model = (table, opts = {}) => {
    let _model = {};
    _model._table = table;
    //opt
    _model._connection = opts.connection || null;
    _model._store = opts.store || '';
    _model._reload = opts.reload || false;

    _model.field = (fields) => {
        _model._field = fields;
        return _model;
    }
    _model.where = (where, ...params) => {
        _model._where = ' WHERE ' + where.replace('&&', ' AND ').replace('||', ' OR ');
        _model._where.replace('  ', ' ');
        _model._params = params;
        return _model;
    }
    _model.whereExtra = (where, ...params) => {
        if (js.isNullOrUndefined(_model._where)) _model._where = ' WHERE ';
        _model._where += where.replace('&&', ' AND ').replace('||', ' OR ');
        _model._where.replace('  ', ' ');
        _model._params = params;
        return _model;
    }
    _model.join = (join) => {
        _model._table += ' LEFT JOIN ' + join;
        return _model;
    }
    _model.rightJoin = (join) => {
        _model._table += ' RIGHT JOIN ' + join;
        return _model;
    }
    _model.innerJoin = (join) => {
        _model._table += ' INNER JOIN ' + join;
        return _model;
    }
    _model.orderBy = (order) => {
        _model._order = ' ORDER BY ' + order;
        return _model;
    }
    _model.groupBy = (group) => {
        _model._order = ' GROUP BY ' + group;
        return _model;
    }
    /** 查询记录
     * 
     * @param {int} page 第几页,从1开始,默认为1
     * @param {int} size 每页条数,默认为1000
     * @returns db.start() return object
     * @example
     * let model = await db.model('`t_users`').select(1, 20);
     * 可用前置包括: 
     * .join()
     * .rightJoin()
     * .innerJoin()
     * .field()
     * .where()
     * .group()
     * .order()
     */
    _model.select = async (page = 1, size = 1000) => {
        let
            table = _model._table,
            field = _model._field || '*',
            where = _model._where || '',
            group = _model._group || '',
            order = _model._order || '',
            limit = ` LIMIT ${(page - 1) * size},${size} `;

        let query = `SELECT ${field} FROM ${table} ${where} ${group} ${order} ${limit}`;

        return this.start(query, _model._params,
            {
                connection: _model._connection,
                store: _model._store,
                reload: _model._reload,
            });
    }
    /** 返回首行数据
     * 若无数据则抛出err错误,若err为空,则不抛出错误并返回空对象{} 
     * 若指定resField,则只返回resField字段
    */
    _model.shift = (err, resField) => {
        let
            table = _model._table,
            field = _model._field || '*',
            where = _model._where || '',
            group = _model._group || '',
            order = _model._order || '',
            limit = ` LIMIT 0,1 `;

        let query = `SELECT ${field} FROM ${table} ${where} ${group} ${order} ${limit}`;

        return this.shift(err, resField, query, _model._params,
            {
                connection: _model._connection,
                store: _model._store,
                reload: _model._reload,
            });
    }
    /** 快速查询总条数
     * 
     */
    _model.count = (err = '') => {
        return _model.field('count(1) as c').shift(`查询${err}总数量错误`, 'c');
    }
    /** 插入记录
     * 
     * @param {object} params 字段和参数
     * @returns 最后插入的自增自动值
     * @example
     * //单挑插入
     * let lastID = await db.model('t_admin').insert({ account: '999', pwd: 'pwd999', nick: 'dd' });
     * 
     * //批量插入
     * //批量插入中,返回的lastID=第一条数据的id
       //本测试中为999的id
       let postData = [
           { account: '999', pwd: 'pwd999', nick: 'dd' },
           { account: '998', pwd: 'pwd998', nick: 'dd' },
           { account: '997', pwd: 'pwd997', nick: 'dd' }]
       let lastID = await db.model('`t_admin`').insert(postData);
     */
    _model.insert = async (params = {}) => {
        let table = _model._table;
        let field = '', values = [];
        if (!js.isArrayObject(params)) {
            //单条插入
            field = '`' + Object.keys(params).join('`,`') + '`';
            values.push(Object.values(params));
        }
        else {
            //批量插入
            field = '`' + Object.keys(params[0]).join('`,`') + '`';
            params.forEach(elemt => {
                values.push(Object.values(elemt));
            })
        }

        let query = `INSERT INTO ${table} (${field}) VALUES ?`
        let result = await this.start(query, [values], { connection: _model._connection });
        return result.insertId;
    }
    /** 更新记录, 调用前必须有where
     * 
     * @param {object} params 字段和参数
     * @example
     * await db.model('`t_admin`').where('`account`=?', '997').update({ nick: 'dd九九七' });
     */
    _model.update = async (params = {}) => {
        let
            table = _model._table,
            where = _model._where || '';
        if (where == '') throw '危险操作,已禁止!';

        let set = '`' + Object.keys(params).join('`=?,`') + '`=?';

        params = Object.values(params).concat(_model._params);

        let query = `UPDATE ${table} SET ${set} ${where}`;
        return await this.start(query, params, { connection: _model._connection });
    }
    /** 更新记录, 调用前必须有where, 要求必须有影响的行
     * 
     * @param {object} params 字段和参数
     * @example
     * await db.model('`t_admin`').where('`account`=?', '997').update({ nick: 'dd九九七' });
     */
    _model.relyUpdate = async (err, params = {}) => {
        let result = await _model.update(params);

        if (result.affectedRows <= 0) {
            js.throwObj(err);
        }
        return;
    }
    /** 更新记录, 调用前必须有where
     * 
     * @param {object} params 字段和参数
     * @example
     * await db.model('`t_admin`').where('`account`=?', '997').updateSql('count=count+?',1);
     */
    _model.updateSql = async (sql, ...params) => {
        let
            table = _model._table,
            where = _model._where || '';
        if (where == '') throw '危险操作,已禁止!';

        params = params.concat(_model._params);

        let query = `UPDATE ${table} SET ${sql} ${where}`;
        await this.start(query, params, { connection: _model._connection });
        return;
    }
    /** 更新记录, 调用前必须有where,要求必须有影响的行
     * 
     * @param {object} params 字段和参数
     * @example
     * await db.model('`t_admin`').where('`account`=?', '997').updateSql('更新失败','count=count+?',1);
     */
    _model.relyUpdateSql = async (err, sql, ...params) => {
        let result = await _model.updateSql(sql, params);

        if (result.affectedRows <= 0) {
            js.throwObj(err);
        }
        return;
    }
    /** 更新或插入记录, 以PRIMARY键或者UNIQUE键为判断依据,更新或插入数据
     * 
     * @param {object} params 字段和参数
     * @param {String} autoField 自增字段名称, 默认为'id', 若该自动设为'', 则不会返回本次操作的记录id
     * @example
     * let lastID = await db.model('t_admin').insertOrUpdate({account: '99999', pwd: 'pwd999', nick: 'ddddd' });
     * let lastID = await db.model('t_admin').insertOrUpdate({account: '99999', pwd: 'pwd999' }, 'pwd=?,money=money+?',100,1);
     */
    _model.insertOrUpdate = async (params = {}, updateSql, ...updateParams) => {
        let
            table = _model._table,
            field = '', field_v = '', set = '', _params = [];

        if (!js.isArrayObject(params)) {
            //单条插入

            field = Object.keys(params);
            for (let i = 0; i < field.length; i++) {
                field_v += '?,';
            }
            field_v = '(' + field_v.substr(0, field_v.length - 1) + ')';
            field = '`' + field.join('`,`') + '`';
        } else {
            //批量插入
            field_v = [];
            field = '`' + Object.keys(params[0]).join('`,`') + '`';
            for (let i = 0; i < params.length; i++) {
                field_v[i] = '(?)';
            }
            field_v = field_v.toString(',');
        }

        if (updateSql) {
            set = updateSql

            let _pms = [];
            params.forEach(element => {
                _pms = [..._pms, Object.values(element)]
            });

            _params = [..._pms, ...updateParams];
        } else {
            set = Object.keys(params);
            set.shift();
            set = '`' + set.join('`=?,`') + '`=?'

            let _pms = Object.values(params);
            _pms.shift();
            _params = [...Object.values(params), ..._pms];
        }
        // if (autoField) set = ` ${autoField}=LAST_INSERT_ID(${autoField}), ` + set;

        let query = `INSERT INTO ${table}(${field})VALUES${field_v} ON DUPLICATE KEY UPDATE ${set}`
        let result = await this.start(query, _params, { connection: _model._connection });
        return result.insertId;

        //不查询lastId
        if (!autoField) return;

        let dbObj = await this.start('SELECT LAST_INSERT_ID();');
        return dbObj.shift()['LAST_INSERT_ID()'];
    }
    /** 删除记录, 调用前必须有where
     * @example
     * await db.model('`t_admin`').where('`nick` like ?', 'dd%').delete();
     */
    _model.delete = async () => {
        let
            table = _model._table,
            where = _model._where || '';
        if (where == '') throw '危险操作,已禁止!';
        let query = `DELETE FROM ${table} ${where}`;
        await this.start(query, _model._params, { connection: _model._connection });
        return;
    }


    return _model;
}


/**
 * @param {String} query 查询语句
 * @param {ObjectArray} params 参数数组
 * @param {*} [opts-connection] 数据库连接,如果连接从外部传入,则同样需要外部调用release
 * @param {String} [opts-store] 是否优先从缓存中获取(依赖conCtl.store)
 * @param {bool} [opts-reload] 是否强制更新缓存(依赖conCtl.store)
 * @example 
 * let usinfo = await db.start('SELECT * FROM `t_users`');
 * cc.send(c, result);
 * 
   //如果必须要查询到某个值,可以这样写
   let uinfo = await db.shift('没找到会报错','','SELECT * FROM `t_users` WHERE `openid`=?',[openid]);//找到的话会返回result[0]

   从缓存读取
   await db.start('SELECT * FROM `t_users`', {store: 'store.t_users'})
   更新缓存
   await db.start('SELECT * FROM `t_users`', {reload: true})
 */

module.exports.start = (query, params = [], opts = {}) => {
    return new Promise(async (r, e) => {

        if (params && (params.store || params.reload)) { opts = params; params = []; }
        let connection = opts.connection || null;
        let store = opts.store || '';
        let reload = opts.reload || false;

        let data = []

        if (store && !reload) {
            let val = db_store.get(store);
            if (val) {
                r(val);
                return;
            }
        }

        let run = () => {
            // cc.log(query, params);//todo test code
            connection.query(query, params || [], (err, result) => {
                if (err) {
                    if (err.code == 'ER_DUP_ENTRY') {
                        let errKey = err.message.split(' for key ')[1];
                        if (errKey == "'PRIMARY'") errKey = '主键重复';
                        e(errKey);
                    }
                    else if (err.code == 'WARN_DATA_TRUNCATED') {
                        e('输入内容错误，或内容过长');
                    }
                    else {
                        cc.err(err);
                        e(`(db query error) ${js.rescode.db_fail} : 服务器异常，请稍后再试。`);
                    }
                    return;
                }

                data = result;
                r(data);

                //保存缓存
                if (store || reload) {
                    db_store.set(store, data, conf.memory_db_cache_outtime);//保存10分钟缓存
                }

            });
        }

        connection ?
            run() :
            pool.getConnection((err, conn) => {
                if (err && cc.err(err)) {
                    e(`(db connection error) ${js.rescode.db_fail} : 服务器异常，请稍后再试。`);
                    return;
                }
                connection = conn;
                run();
                connection.release();
            });

    })//Promise end
};

/** 返回查询到的首行数据
 * 
 */
module.exports.shift = (err, resField, query, params = [], opts = {}) => {
    return new Promise(async (r, e) => {
        await this.start(query, params, opts).then(data => {
            if (!data.length) {
                if (err) e(err);
                if (resField) r(''); else r({});
            }
            if (resField)
                r(data[0][resField]);
            else
                r(data[0]);
        }).catch(async _e => {
            e(_e)
        });
    })
}

/** 用于在 mysql.Transaction 中串行执行查询
 * 
 * @param {Function} func 要执行的函数
 * @example
    await db.startTransaction(async (tran) => {
        let usinfo = await tran.start('SELECT * FROM `t_users`');
        let admininfo = await tran.start('SELECT * FROM `t_admin`');
        cc.send(c, { usinfo, admininfo });
    })
 */
module.exports.startTransaction = (func) => {
    return new Promise(async (r, e) => {

        pool.getConnection((err, conn) => {
            if (err && cc.err(err)) {
                e(`(db connection error) ${js.rescode.db_fail} : 服务器异常，请稍后再试。`);
                return;
            }
            connection = conn;
            connection.beginTransaction(async err => {
                if (err && cc.err(err)) {
                    connection.release();
                    e(`(db transaction error) ${js.rescode.db_fail} : 服务器异常，请稍后再试。`);
                    return;
                }

                try {
                    await func({
                        start: async (query, params = [], opts = {}) => {
                            if (params && (params.store || params.reload)) { opts = params; params = []; }
                            let store = opts.store || '';
                            let reload = opts.reload || false;
                            return this.start(query, params, { connection, store, reload });
                        },
                        shift: async (err, resField, query, params = [], opts = {}) => {
                            if (params && (params.store || params.reload)) { opts = params; params = []; }
                            let store = opts.store || '';
                            let reload = opts.reload || false;
                            return this.shift(err, resField, query, params, { connection, store, reload });
                        },
                        model: (table, opts = {}) => {
                            let store = opts.store || '';
                            let reload = opts.reload || false;
                            return this.model(table, { connection, store, reload })
                        }
                    })
                } catch (err) {
                    connection.rollback();
                    connection.release();
                    e(err);
                    return;
                }

                connection.commit(err => {
                    if (err && cc.err(err)) {
                        connection.rollback();
                        e(`(db transaction commit error) ${js.rescode.db_fail} : 服务器异常，请稍后再试。`);
                        return;
                    }
                    connection.release();
                    r();
                })
            });
        });

    })//Promise end
}
